﻿using Model;
using System;
using System.Collections.Generic;
using System.Data;

namespace BLL
{
    public class QS_URGENTFUNDSAPPLYBLL : BaseBLL
    {
        public QS_URGENTFUNDSAPPLY Find(string buid)
        {
            string sql = string.Format(@"select * from qs_urgentfundsapply t where t.n_business_id =" + buid);

            DataTable dt = Query(sql, "", "Find", "查询紧急资金", "");

            QS_URGENTFUNDSAPPLY model = new QS_URGENTFUNDSAPPLY();

            if (dt != null && dt.Rows.Count > 0)
            {
                model.N_URGENT_ID = decimal.Parse(dt.Rows[0]["N_URGENT_ID"].ToString());
                model.N_AMOUNT = decimal.Parse(dt.Rows[0]["N_AMOUNT"].ToString());
                model.C_ACCOUNT = dt.Rows[0]["C_ACCOUNT"].ToString();
                model.C_EVENT = dt.Rows[0]["C_EVENT"].ToString();

                model.D_DATA = DateTime.Parse(dt.Rows[0]["D_DATA"].ToString());
                model.N_BUSINESS_ID = decimal.Parse(dt.Rows[0]["N_BUSINESS_ID"].ToString());
                model.N_BUSINESS_STATE = decimal.Parse(dt.Rows[0]["N_BUSINESS_STATE"].ToString());

                model.N_USER_ID = decimal.Parse(dt.Rows[0]["N_USER_ID"].ToString());

                model.D_OPERATOR_TIME = DateTime.Parse(dt.Rows[0]["D_OPERATOR_TIME"].ToString());
                model.N_COMPANY_ID = decimal.Parse(dt.Rows[0]["N_COMPANY_ID"].ToString());

                model.C_REMARK = dt.Rows[0]["C_REMARK"].ToString();
                model.C_ACCOUNT_NAME = dt.Rows[0]["C_ACCOUNT_NAME"].ToString();
                model.C_TITLE = dt.Rows[0]["C_TITLE"].ToString();
            }
            return model;
        }

        public int Update(QS_URGENTFUNDSAPPLY model, string operName)
        {
            string sql = @"update qs_urgentfundsapply
   set   n_amount = " + model.N_AMOUNT + @",
       c_account = '" + model.C_ACCOUNT + @"',
       c_event = '" + model.C_EVENT + @"',
       d_data =to_date('" + model.D_DATA.ToString("yyyy-MM-dd") + @"','yyyy-mm-dd'),
       c_remark = '" + model.C_REMARK + @"',
       c_account_name = '" + model.C_ACCOUNT_NAME + @"',
       c_title = '" + model.C_TITLE + @"'
 where n_business_id = " + model.N_BUSINESS_ID;

            return NoQuery(sql, operName);
        }

        /// <summary>
        /// 紧急资金申请
        /// </summary>
        /// <param name="bModle"></param>
        /// <param name="model"></param>
        /// <param name="tModel"></param>
        /// <param name="NexModel"></param>
        /// <param name="logname"></param>
        /// <returns></returns>
        public int AppUrgent(QS_Business bModle, QS_URGENTFUNDSAPPLY model, Lc_Transaction tModel, Lc_Transaction NexModel, string logname)
        {
            List<string> cList = new List<string>();
            string sql = @"insert into qs_business
  (n_business_id,
   n_business_name,
   n_process_id,
   n_business_state,
   n_business_operator_id,
   n_business_operator_time,
   n_remark,
   n_company_id)
values
  (" + bModle.Business_Id + @",
   '" + bModle.Business_Name + @"',
   " + bModle.Process_id + @",
  " + bModle.Business_State + @",
   " + bModle.Business_operator_Id + @",
   sysdate,
 '" + bModle.Business_remark + @"',
      " + bModle.N_COMPANY_ID + @")";

            cList.Add(sql);

            sql = string.Format(@"insert into qs_urgentfundsapply
  (n_urgent_id,
   n_amount,
   c_account,
   c_event,
   d_data,
   n_business_id,
   n_business_state,
   n_user_id,
   d_operator_time,
   n_company_id,
   c_remark,
   c_account_name,C_TITLE)
values
  (SEQ_URGENT_ID.NEXTVAL,
   {0},
   '{1}',
   '{2}',
   to_date('{3}','yyyy-mm-dd'),
   {4},
   {5},
   {6},
   sysdate,
   {7},
   '{8}',
   '{9}','{10}')", model.N_AMOUNT, model.C_ACCOUNT, model.C_EVENT, model.D_DATA.ToString("yyyy-MM-dd"), model.N_BUSINESS_ID,
                     model.N_BUSINESS_STATE, model.N_USER_ID, model.N_COMPANY_ID, model.C_REMARK, model.C_ACCOUNT_NAME, model.C_TITLE);
            cList.Add(sql);

            #region MyRegion

            sql = @"insert into lc_transaction
  (n_transaction_id,
   n_process_id,
   n_queue_id,
   n_business_id,
   n_transaction_state,
   n_transaction_comment,
   c_transactor_id,
   d_transaction_time_begin,
   d_transaction_time_end,
   d_transaction_time_due,
   n_transaction_due_state,
   c_transactor_signature,
   n_last_bace_transaction_id)
values
  (n_transaction_id.Nextval,
   " + tModel.N_PROCESS_ID + @",
   " + tModel.N_QUEUE_ID + @",
   " + bModle.Business_Id + @",
   " + tModel.N_TRANSACTION_STATE + @",
   '" + tModel.C_TRANSACTION_COMMENT + @"',
   '" + tModel.C_TRANSACTOR_ID + @"',
   sysdate,
   sysdate,
   TO_DATE('" + tModel.D_TRANSACTION_TIME_DUE + @"','YYYY-MM-DD HH24:MI:SS'),
    " + tModel.N_TRANSACTION_DUE_STATE + @",
   '" + tModel.C_TRANSACTOR_SIGNATURE + @"',
   " + tModel.N_LAST_BACE_TRANSACTION_ID + @")";

            cList.Add(sql);

            sql = @"insert into lc_transaction
  (n_transaction_id,
   n_process_id,
   n_queue_id,
   n_business_id,
   n_transaction_state,
   n_transaction_comment,
   c_transactor_id,
   d_transaction_time_begin,
   d_transaction_time_end,
   d_transaction_time_due,
   n_transaction_due_state,
   c_transactor_signature,
   n_last_bace_transaction_id)
values
  (n_transaction_id.Nextval,
   " + NexModel.N_PROCESS_ID + @",
   " + NexModel.N_QUEUE_ID + @",
   " + bModle.Business_Id + @",
   " + NexModel.N_TRANSACTION_STATE + @",
   '" + NexModel.C_TRANSACTION_COMMENT + @"',
   '" + NexModel.C_TRANSACTOR_ID + @"',
   sysdate,
   TO_DATE('1900-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS'),
   TO_DATE('" + NexModel.D_TRANSACTION_TIME_DUE + @"','YYYY-MM-DD HH24:MI:SS'),
    " + NexModel.N_TRANSACTION_DUE_STATE + @",
   '" + NexModel.C_TRANSACTOR_SIGNATURE + @"',
   " + NexModel.N_LAST_BACE_TRANSACTION_ID + @")";

            #endregion MyRegion

            cList.Add(sql);
            //, "紧急资金申请", "AppUrgent", logname, logname
            int rec = SQLSExcute(cList);
            return rec;
        }

        public int GetRecordCount(out decimal amt, string whereStr, string operName)
        {
            string sql = string.Format(@"select count(1),NVL(sum(n_amount),0)
  from qs_urgentfundsapply t, sys_company c, sys_user u
 where t.n_company_id = c.cmy_id
   and t.n_user_id = u.user_id " + whereStr);

            DataTable dt = Query(sql);
            int count = int.Parse(dt.Rows[0][0].ToString());

            amt = decimal.Parse(dt.Rows[0][1].ToString());

            return count;
        }

        /// <summary>
        /// 紧急资金明细
        /// </summary>
        /// <param name="whereStr"></param>
        /// <param name="orderby"></param>
        /// <param name="startIndex"></param>
        /// <param name="endIndex"></param>
        /// <returns></returns>
        public DataTable GetDataListByPage(string whereStr, string orderby, int startIndex, int endIndex)
        {
            string sql = string.Format(@"SELECT * FROM (select ROW_NUMBER() OVER(ORDER BY " + orderby + @") as r,
       c.cmy_name,
       t.n_amount,
       to_char(t.d_data, 'yyyy-mm-dd') d_data,
       t.c_title,
       t.c_account,
       t.c_account_name,
       t.c_event,
       t.c_remark,
       decode(t.n_business_state, 0, '注销', 1, '办理中', 2, '已归档') n_business_state,
       u.user_name, t.d_operator_time
  from qs_urgentfundsapply t, sys_company c, sys_user u
 where t.n_company_id = c.cmy_id
   and t.n_user_id = u.user_id ");

            if (!string.IsNullOrEmpty(whereStr))
            {
                sql += whereStr;
            }

            sql += @" ) TT";
            if (startIndex >= 2)
            {
                sql += " WHERE TT.r BETWEEN (" + startIndex + "-1)*" + endIndex + "+1 AND " + startIndex + "*" + endIndex + "";
            }
            else
            {
                sql += " WHERE TT.r BETWEEN (" + startIndex + "-1)*" + endIndex + " AND " + startIndex + "*" + endIndex + "";
            }
            return Query(sql, "", "GetDataListByPage", "紧急资金明细", "");
        }
    }
}